package org.longhua.replace;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.BufferedWriter;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.io.StringWriter;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.longhua.common.util.ExcelToHtmlConverter;
import org.longhua.mode.BankData;
import org.w3c.dom.Document;

public class ExcelToHtml extends HttpServlet {
	public static final String FILE_SEPARATOR = System.getProperties()
			.getProperty("file.separator");

	@Override
	protected void doPost(HttpServletRequest request,
			HttpServletResponse response) throws ServletException, IOException {
		doGet(request, response);
	}

	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		String docsPath = request.getSession().getServletContext()
				.getRealPath("docs");
		String templateName = "D://temple2.xls";// Excel模版
		String templateName2 = "D://test.html";// Excel模版
//		String templatePath = docsPath + FILE_SEPARATOR + templateName;
//		String templatePath2 = docsPath + FILE_SEPARATOR + templateName2;
//		File file = new File(templatePath);
//		File file2 = new File(templatePath2);
		POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
				templateName));
		HSSFWorkbook wb = new HSSFWorkbook(fs);
		ExcelToHtmlConverter excelToHtmlConverter;
		try {
			excelToHtmlConverter = new ExcelToHtmlConverter (DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument());
			excelToHtmlConverter.setOutputColumnHeaders(false); 
			excelToHtmlConverter.setOutputRowNumbers(false); 
			
			excelToHtmlConverter.processWorkbook(wb); 
			excelToHtmlConverter.getCssClassPrefixTable();
			excelToHtmlConverter.getCssClassPrefixDiv();
		     Document htmlDocument = excelToHtmlConverter.getDocument(); 
		     ByteArrayOutputStream outStream = new ByteArrayOutputStream(); 
		     DOMSource domSource = new DOMSource (htmlDocument); 
		     StreamResult streamResult = new StreamResult (outStream); 
		     TransformerFactory tf = TransformerFactory.newInstance(); 
		     Transformer serializer = tf.newTransformer(); 
		     serializer.setOutputProperty (OutputKeys.ENCODING, "gb2312"); 
		     serializer.setOutputProperty (OutputKeys.INDENT, "yes"); 
		     serializer.setOutputProperty (OutputKeys.METHOD, "html"); 
		     serializer.transform (domSource, streamResult); 
		     outStream.close();
			writeFile(new String(outStream.toByteArray()), templateName2);  
			
			
		} catch (ParserConfigurationException | TransformerException e) {
			e.printStackTrace();
		} 
		
	}
	
	
	  public static void writeFile(String content, String path) {  
	        FileOutputStream fos = null;  
	        BufferedWriter bw = null;  
	        try {  
	            File file = new File(path);  
	            fos = new FileOutputStream(file);  
	            bw = new BufferedWriter(new OutputStreamWriter(fos,"GB2312"));  
	            bw.write(content);  
	        } catch (FileNotFoundException fnfe) {  
	            fnfe.printStackTrace();  
	        } catch (IOException ioe) {  
	            ioe.printStackTrace();  
	        } finally {  
	            try {  
	                if (bw != null)  
	                    bw.close();  
	                if (fos != null)  
	                    fos.close();  
	            } catch (IOException ie) {  
	            }  
	        }  
	    }  
	
	
	

	
	public static void convertExcel2Html(String excelFilePath,String htmlFilePath)  
		    throws IOException,ParserConfigurationException,TransformerException  
		    {  
		        File excelFile = new File(excelFilePath);  
		        File htmlFile = new File(htmlFilePath);  
		        File htmlFileParent = new File(htmlFile.getParent());  
		        InputStream is = null;  
		        OutputStream out = null;  
		        StringWriter writer = null;  
		        try{  
		            if(excelFile.exists()){  
		                if(!htmlFileParent.exists()){  
		                    htmlFileParent.mkdirs();  
		                }  
		                is = new FileInputStream(excelFile);  
		                HSSFWorkbook workBook = new HSSFWorkbook(is);  
		                ExcelToHtmlConverter converter = new ExcelToHtmlConverter(DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument());  
		                  
		                converter.processWorkbook(workBook);  
		                  
		                writer = new StringWriter();  
		                Transformer serializer = TransformerFactory.newInstance().newTransformer();  
		                serializer.setOutputProperty(OutputKeys.ENCODING, "UTF-8");  
		                serializer.setOutputProperty(OutputKeys.INDENT, "yes");  
		                serializer.setOutputProperty(OutputKeys.METHOD, "html");  
		                serializer.transform(  
		                        new DOMSource(converter.getDocument()),  
		                        new StreamResult(writer) );  
		                out = new FileOutputStream(htmlFile);  
		                out.write(writer.toString().getBytes("UTF-8"));  
		                out.flush();  
		                out.close();  
		                writer.close();  
		            }  
		        }finally{  
		            try{  
		                if(is != null){  
		                    is.close();  
		                }  
		                if(out != null){  
		                    out.close();  
		                }  
		                if(writer != null){  
		                    writer.close();  
		                }  
		            }catch(IOException e){  
		                e.printStackTrace();  
		            }  
		        }  
		    }  
	
	
	public static boolean replaceModel(List<BankData> datas,
			String sourceFilePath, String targetFilePath) {
		boolean bool = true;
		try {
			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(
					sourceFilePath));
			HSSFWorkbook wb = new HSSFWorkbook(fs);
			HSSFSheet sheet = wb.getSheetAt(0);

			Integer tolmonitoring = 0;
			// 预警量 tol
			Integer tolwarning = 0;
			// 下发量 tol
			Integer tolsendDown = 0;
			// 按时处置回复量 tol
			Integer tolreplyNormal = 0;
			// 驳回量 tol
			Integer tolreject = 0;
			// 确认违规量 tol
			Integer tolviolatCount = 0;
			// 处罚量 tol
			Integer tolpunish = 0;
			// 整改量 tol
			Integer tolupdate = 0;
			// 违规人数 tol
			Integer tolviolatorCount = 0;
			// 问责处罚人数 tol
			Integer tolpunishPeo = 0;
			// 员工违规积分 tol
			Integer tolemployeeIntegral = 0;
			// 机构风险扣分 tol
			Integer tolorganizationIntegral = 0;
			// 风险积分名次 tol
			Integer tolrankingIntegral = 0;

			int j = 0;
			int newrow = 2;
			for (int i = 0; i < datas.size(); i++) {
				// 去除head部分
				HSSFRow row = sheet.getRow(newrow);
				if(i==3){
					row.createCell(0).setCellValue("分行");
				}else{
					row.createCell(0).setCellValue("石家庄分行");
				}
				//row.createCell(0).
				row.createCell(1).setCellValue("笔数");
				row.createCell(2).setCellValue(datas.get(i).getMonitoring());
				row.createCell(3).setCellValue(datas.get(i).getWarning());
				row.createCell(4).setCellValue(datas.get(i).getSendDown());
				row.createCell(5).setCellValue(datas.get(i).getReplyNormal());
				row.createCell(6).setCellValue(datas.get(i).getReject());
				row.createCell(7).setCellValue(datas.get(i).getViolatCount());
				row.createCell(8).setCellValue(datas.get(i).getPunish());
				row.createCell(9).setCellValue(datas.get(i).getUpdate());
				row.createCell(10)
						.setCellValue(datas.get(i).getViolatorCount());
				row.createCell(11).setCellValue(datas.get(i).getPunishPeo());
				row.createCell(12).setCellValue(
						datas.get(i).getEmployeeIntegral());
				row.createCell(13).setCellValue(
						datas.get(i).getOrganizationIntegral());
				row.createCell(14).setCellValue(
						datas.get(i).getRankingIntegral());
				//newrow = newrow + 3;
				newrow++;
				// 总笔数
				tolmonitoring = tolmonitoring + datas.get(i).getMonitoring();
				tolwarning = tolwarning + datas.get(i).getWarning();
				tolsendDown = tolsendDown + datas.get(i).getSendDown();
				tolreplyNormal = tolreplyNormal + datas.get(i).getReplyNormal();
				tolreject = tolreject + datas.get(i).getReject();
				tolviolatCount = tolviolatCount + datas.get(i).getViolatCount();
				tolpunish = tolpunish + datas.get(i).getPunish();
				tolupdate = tolupdate + datas.get(i).getUpdate();
				tolviolatorCount = tolviolatorCount
						+ datas.get(i).getViolatorCount();
				tolpunishPeo = tolpunishPeo + datas.get(i).getPunishPeo();
				tolemployeeIntegral = tolemployeeIntegral
						+ datas.get(i).getEmployeeIntegral();
				tolorganizationIntegral = tolorganizationIntegral
						+ datas.get(i).getOrganizationIntegral();
				// tolrankingIntegral = tolrankingIntegral +
				// datas.get(i).getRankingIntegral();
			}
			
			
			
			HSSFRow row = sheet.getRow(newrow);
			row.createCell(2).setCellValue(tolmonitoring);
			row.createCell(3).setCellValue(tolwarning);
			row.createCell(4).setCellValue(tolsendDown);
			row.createCell(5).setCellValue(tolreplyNormal);
			row.createCell(6).setCellValue(tolreject);
			row.createCell(7).setCellValue(tolviolatCount);
			row.createCell(8).setCellValue(tolpunish);
			row.createCell(9).setCellValue(tolupdate);
			row.createCell(10).setCellValue(tolviolatorCount);
			row.createCell(11).setCellValue(tolpunishPeo);
			row.createCell(12).setCellValue(tolemployeeIntegral);
			row.createCell(13).setCellValue(tolorganizationIntegral);
			// row.createCell(14).setCellValue(tolrankingIntegral);
			// 输出文件
			mergerData(wb,0,0,2);
			
			FileOutputStream fileOut = new FileOutputStream(targetFilePath);
			wb.write(fileOut);
			fileOut.close();
		} catch (Exception e) {
			bool = false;
			e.printStackTrace();
		}
		return bool;
	}

	private void download(String path, HttpServletResponse response) {
		try {
			// path是指欲下载的文件的路径。
			File file = new File(path);
			// 取得文件名。
			String filename = file.getName();
			// 以流的形式下载文件。
			InputStream fis = new BufferedInputStream(new FileInputStream(path));
			byte[] buffer = new byte[fis.available()];
			fis.read(buffer);
			fis.close();
			// 清空response
			response.reset();
			// 设置response的Header
			response.addHeader("Content-Disposition", "attachment;filename="
					+ new String(filename.getBytes()));
			response.addHeader("Content-Length", "" + file.length());
			OutputStream toClient = new BufferedOutputStream(
					response.getOutputStream());
			response.setContentType("application/vnd.ms-excel;charset=gb2312");
			toClient.write(buffer);
			toClient.flush();
			toClient.close();
		} catch (IOException ex) {
			ex.printStackTrace();
		}
	}
	
	private static HSSFWorkbook mergerData(HSSFWorkbook workbook, int sheetName, int colIndex, int startRowIndex) {
		HSSFSheet sheet = workbook.getSheetAt(sheetName);
		int lastRowNum = sheet.getLastRowNum();

		for (int i = startRowIndex; i <= lastRowNum; i++) {
		HSSFCell cell = sheet.getRow(i).getCell(colIndex);

		for (int j = i + 1; j <= lastRowNum; j++) {
		HSSFCell celltemp = sheet.getRow(j).getCell(colIndex);

		// 如果下一行与被比较行相等，则继续该循环，直到不等才跳出
		if (!celltemp.getStringCellValue().equals(cell.getStringCellValue())) {
		if (j - 1 > i) {
		// 合并单元格
		sheet.addMergedRegion(new CellRangeAddress(i, j - 1, colIndex, colIndex));
		}
		i = j-1;
		break;
		}
		if(j == lastRowNum){
		sheet.addMergedRegion(new CellRangeAddress(i, lastRowNum, colIndex, colIndex));
		}
		}
		}

		return workbook;
		}
	
	private static HSSFWorkbook mergerData2(HSSFWorkbook workbook, int sheetName, int colIndex, int startRowIndex) {
		HSSFSheet sheet = workbook.getSheetAt(sheetName);
		int lastRowNum = sheet.getLastRowNum();

		for (int i = startRowIndex; i <= lastRowNum; i++) {
		HSSFCell cell = sheet.getRow(i).getCell(colIndex);

		for (int j = i + 1; j <= lastRowNum; j++) {
		HSSFCell celltemp = sheet.getRow(j).getCell(colIndex);

		// 如果下一行与被比较行相等，则继续该循环，直到不等才跳出
		if ("".equals(cell.getStringCellValue())||null == cell.getStringCellValue()) {
		if (j - 1 > i) {
		// 合并单元格
		sheet.addMergedRegion(new CellRangeAddress(i, j - 1, colIndex, colIndex));
		}
		i = j-1;
		break;
		}
		if(j == lastRowNum){
		sheet.addMergedRegion(new CellRangeAddress(i, lastRowNum, colIndex, colIndex));
		}
		}
		}

		return workbook;
		}
	
}